package quanlydoanvao.themmoi;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import javax.faces.model.SelectItem;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import dbutil.ConnectDB;

import quanlydoanra.sua.SuaDoanRa;
import quanlydoanra.themmoi.MucDich;
import quanlydoanra.themmoi.QuyetDinh;
import quanlydoanra.utils.TableNguoiDi;
import quanlydoanra.utils.Utils;
import quanlydoanra.utils.managerID;
import quanlydoanra.utils.truyXuatDatabase;
import quanlydoanvao.sua.SuaDoanVao;
import utils.DateUtils;
import utils.UtilsOnScopes;
import utils.Validate;

public class DoanVao {
	private String maDoan;
	private String diaDiemDen;
	private java.util.Date ngayDen;
	private java.util.Date ngayVe;
	private String moTa;
	private String linhVuc;
	private String mucDich;
	private SelectItem[] listMucDich = null;
	private String errorTTChung;
	
	//thong tin van ban den
	private String errorVBDen;
	private String soVB;
	private String cqDeNghi;
	private java.util.Date ngayXuatVB;
	private String noiDungVBDen;
	private String moTaQDDen;
	
	//Thong tin van ban nhan
	private String errorVBNhan;
	private String soUBNDTinh;
	private java.util.Date ngayPhatHanh;
	private boolean baoCao;
	private String nguoiChuTri;
	private String moTaQDNhan;
	
	private List<NguoiVao> tablesNguoiVao;
	private List<SuaDoanVao> dsDoanVao = null;

	public DoanVao() {
	}

	public DoanVao(String maDoan, String diaDiemDen, Date ngayDen, Date ngayVe,
			String moTa, String linhVuc, String mucDich, String errorTTChung,
			String idErrorVBDen, String soVB, String cqDeNghi, Date ngayXuatVB,
			String noiDungVBDen, String moTaQDDen, String errorVBNhan,
			String soUBNDTinh, java.util.Date ngayPhatHanh, boolean baoCao,
			String nguoiChuTri, String moTaQDNhan, List<NguoiVao> tablesNguoiVao) {
		this.maDoan = maDoan;
		this.diaDiemDen = diaDiemDen;
		this.ngayDen = ngayDen;
		this.ngayVe = ngayVe;
		this.moTa = moTa;
		this.linhVuc = linhVuc;
		this.mucDich = mucDich;
		this.errorTTChung = errorTTChung;
		this.errorVBDen = idErrorVBDen;
		this.soVB = soVB;
		this.cqDeNghi = cqDeNghi;
		this.ngayXuatVB = ngayXuatVB;
		this.noiDungVBDen = noiDungVBDen;
		this.moTaQDDen = moTaQDDen;
		this.errorVBNhan = errorVBNhan;
		this.soUBNDTinh = soUBNDTinh;
		this.ngayPhatHanh = ngayPhatHanh;
		this.baoCao = baoCao;
		this.nguoiChuTri = nguoiChuTri;
		this.moTaQDNhan = moTaQDNhan;
		this.tablesNguoiVao = tablesNguoiVao;
	}

	public String getMaDoan() {
		return maDoan;
	}

	public void setMaDoan(String maDoan) {
		this.maDoan = maDoan;
	}

	public String getDiaDiemDen() {
		return diaDiemDen;
	}

	public void setDiaDiemDen(String diaDiemDen) {
		this.diaDiemDen = diaDiemDen;
	}

	public java.util.Date getNgayDen() {
		return ngayDen;
	}

	public void setNgayDen(java.util.Date ngayDen) {
		this.ngayDen = ngayDen;
	}

	public java.util.Date getNgayVe() {
		return ngayVe;
	}

	public void setNgayVe(java.util.Date ngayVe) {
		this.ngayVe = ngayVe;
	}

	public String getMoTa() {
		return moTa;
	}

	public void setMoTa(String moTa) {
		this.moTa = moTa;
	}

	public String getLinhVuc() {
		return linhVuc;
	}

	public void setLinhVuc(String linhVuc) {
		this.linhVuc = linhVuc;
	}

	public String getMucDich() {
		return mucDich;
	}

	public void setMucDich(String mucDich) {
		this.mucDich = mucDich;
	}

	public String getErrorTTChung() {
		return errorTTChung;
	}

	public void setErrorTTChung(String errorTTChung) {
		this.errorTTChung = errorTTChung;
	}

	public String getErrorVBDen() {
		return errorVBDen;
	}

	public void setErrorVBDen(String errorVBDen) {
		this.errorVBDen = errorVBDen;
	}

	public String getSoVB() {
		return soVB;
	}

	public void setSoVB(String soVB) {
		this.soVB = soVB;
	}

	public String getCqDeNghi() {
		return cqDeNghi;
	}

	public void setCqDeNghi(String cqDeNghi) {
		this.cqDeNghi = cqDeNghi;
	}

	public java.util.Date getNgayXuatVB() {
		return ngayXuatVB;
	}

	public void setNgayXuatVB(java.util.Date ngayXuatVB) {
		this.ngayXuatVB = ngayXuatVB;
	}

	public String getNoiDungVBDen() {
		return noiDungVBDen;
	}

	public void setNoiDungVBDen(String noiDungVBDen) {
		this.noiDungVBDen = noiDungVBDen;
	}

	public String getMoTaQDDen() {
		return moTaQDDen;
	}

	public void setMoTaQDDen(String moTaQDDen) {
		this.moTaQDDen = moTaQDDen;
	}

	public String getErrorVBNhan() {
		return errorVBNhan;
	}

	public void setErrorVBNhan(String errorVBNhan) {
		this.errorVBNhan = errorVBNhan;
	}

	public String getSoUBNDTinh() {
		return soUBNDTinh;
	}

	public void setSoUBNDTinh(String soUBNDTinh) {
		this.soUBNDTinh = soUBNDTinh;
	}

	public java.util.Date getNgayPhatHanh() {
		return ngayPhatHanh;
	}

	public void setNgayPhatHanh(java.util.Date ngayPhatHanh) {
		this.ngayPhatHanh = ngayPhatHanh;
	}

	public boolean isBaoCao() {
		return baoCao;
	}

	public void setBaoCao(boolean baoCao) {
		this.baoCao = baoCao;
	}

	public String getNguoiChuTri() {
		return nguoiChuTri;
	}

	public void setNguoiChuTri(String nguoiChuTri) {
		this.nguoiChuTri = nguoiChuTri;
	}

	public String getMoTaQDNhan() {
		return moTaQDNhan;
	}

	public void setMoTaQDNhan(String moTaQDNhan) {
		this.moTaQDNhan = moTaQDNhan;
	}

	public List<NguoiVao> getTablesNguoiVao() {		
		List<NguoiVao> list = new ArrayList<NguoiVao>();
		String mD =  this.getMaDoan();
		String SQL = "select * from dsnguoiden as DS where DS.iddoanvao ='"+mD+"' ORDER BY ten";
		ArrayList<HashMap<String, Object>> array = ConnectDB.getDataTypeList(SQL);
		if (array.size() > 0) {
			HashMap<String, Object> hash = new HashMap<String, Object>();
			int stt = 0;
			for (int i = 0; i < array.size(); i++) {
				stt = i + 1;
				hash = (HashMap<String, Object>) array.get(i);
				NguoiVao table = new NguoiVao();
				table.setStt(String.valueOf(stt));
				table.setId(Integer.parseInt(hash.get("id").toString()));
				table.setTen(hash.get("ten").toString());
				table.setSoHC(hash.get("sohc").toString());
				table.setCmnd(hash.get("cmnd").toString());
				table.setQuocTich(hash.get("quoctich").toString());
				table.setNgheNghiep(hash.get("nghenghiep").toString());
				table.setStrNgayCapHC(hash.get("ngaycaphc").toString());
				table.setStrNgayHetHanVisa(hash.get("ngayhethanvisa").toString());
				System.out.println("NGày cấp HC "+table.getNgayCapHC()+ " ngày hết han visa "+table.getNgayHetHanVisa());
				list.add(table);
			}
		}
		this.tablesNguoiVao = list;
		return this.tablesNguoiVao;
		
	}

	public void setTablesNguoiVao(List<NguoiVao> tablesNguoiVao) {
		this.tablesNguoiVao = tablesNguoiVao;
	}
	public boolean checkInputValue(){
	//Khoi thong tin chung
		//ngay ve phai sau ngay den			
		//Cac field ko dc rong		
	//Khoi van ban den
		//Ngay xuat van ban truoc ngay den		
		//Cac field ko dc rong		
	//Khoi van ban nhan
		//Ngay phat hanh van ban truoc ngay den		
		//Cac field ko dc rong
		
		boolean result = false;
		String str ;
		if(this.ngayDen == null || this.ngayDen.equals("") ||
				this.ngayVe == null || this.ngayVe.equals("") ||
				this.diaDiemDen == null || this.diaDiemDen.equals("")||
				this.soVB == null || this.soVB.equals("")||
				this.ngayXuatVB == null || this.ngayXuatVB.equals("") ||
				this.cqDeNghi == null || this.cqDeNghi.equals("")||
				this.noiDungVBDen == null || this.noiDungVBDen.equals("")||
				this.soUBNDTinh == null || this.soUBNDTinh.equals("")||
				this.ngayPhatHanh == null || this.ngayPhatHanh.equals("")||
				this.nguoiChuTri == null || this.nguoiChuTri.equals("")){
		result = false;
		this.setErrorTTChung("Nhập đầy đủ thông tin có dấu (*) phía trước");
			}
		else if(Utils.kiemTraNgay(this.ngayDen, this.ngayVe) == false){
			this.setErrorTTChung("Ngày đến phải trước ngày về! ");
			result = false;
			
		}
		else if(Utils.kiemTraNgay(this.ngayXuatVB, this.ngayDen) == false){
			this.setErrorTTChung("Ngày xuất văn bản đến phải trước ngày đến!");
			result = false;
		}
		else if(Utils.kiemTraNgay(this.ngayPhatHanh, this.ngayDen) == false){
			this.setErrorTTChung("Ngày phát hành văn bản nhận phải trước ngày đến!");
			result = false;
		}
		else if(Utils.kiemTraNgay(this.ngayXuatVB, this.ngayPhatHanh) == false){
			this.setErrorTTChung("Ngày xuất văn bản đến phải trước ngày phát hành văn bản nhận!");
			result = false;
		}
		else {
			this.setErrorTTChung("");
			result = true;
		}
		return result;
	}
	public boolean distincMucDich(List<MucDich> listMucDich,MucDich m){
		boolean result = true;
		for (MucDich l : listMucDich) {
			if(l.getMucdich().equals(m.getMucdich())){
				result = false;
				break;
			}			
		}
		return result;
	}
	public  List<MucDich> dsMucDich(){
		List<MucDich> listMucDich = new ArrayList<MucDich>();
		try{
		String SQL = "SELECT DISTINCT  id,mucdich FROM mucdich where linhvuc ='"+this.linhVuc+"'" ;
		ArrayList array = ConnectDB.getDataTypeList(SQL);
			if (array.size() > 0) {
			HashMap<String, Object> hash = new HashMap<String, Object>();
			for (int i = 0; i < array.size(); i++) {
				MucDich MD = new MucDich();
				hash = (HashMap<String, Object>) array.get(i);
				MD.setId(Integer.parseInt(hash.get("id").toString()));
				MD.setMucdich(hash.get("mucdich").toString());
				if(this.distincMucDich(listMucDich,MD) == true)
					listMucDich.add(MD);
			}
			}
		}
		catch(Exception ex){
			System.out.println("Loi");
		}
		return listMucDich;
	}
	
	public SelectItem[] listItemMucDich() {
		List<MucDich> listFunct = this.dsMucDich();
		SelectItem[] arrItem = new SelectItem[listFunct.size()];
		for (int i = 0; i < listFunct.size(); i++) {
			MucDich FD = listFunct.get(i);
			arrItem[i] = new SelectItem(FD.getMucdich(), FD.getMucdich());
		}
		return arrItem;
	}
	public SelectItem[] loadFirstItem(){
		List<MucDich> listMucDich = new ArrayList<MucDich>();
		try{
		String SQL = "SELECT DISTINCT  id,mucdich FROM mucdich where linhvuc ='Kinh tế'" ;
		ArrayList array = ConnectDB.getDataTypeList(SQL);
		if (array.size() > 0) {
			HashMap<String, Object> hash = new HashMap<String, Object>();
			for (int i = 0; i < array.size(); i++) {
				MucDich MD = new MucDich();
				hash = (HashMap<String, Object>) array.get(i);
				MD.setId(Integer.parseInt(hash.get("id").toString()));
				MD.setMucdich(hash.get("mucdich").toString());
				if(this.distincMucDich(listMucDich,MD) == true)
					listMucDich.add(MD);
			}
		}
		}
		catch(Exception ex){
			System.out.println("Loi");
		}
	
		SelectItem[] arrItem = new SelectItem[listMucDich.size()];
		for (int i = 0; i < listMucDich.size(); i++) {
			MucDich FD = listMucDich.get(i);
			arrItem[i] = new SelectItem(FD.getMucdich(), FD.getMucdich());
		}
		return arrItem;
	}
	
	public void setListMucDich(SelectItem[] listItemMucDich) {
		this.listMucDich = listItemMucDich;
	}
	public String locTheoLinhVucDoanVao(){
		SelectItem[] select = this.listItemMucDich();
		this.setListMucDich(select);
		return "finish_them_doanvao";
	}
	public String locTheoLinhVucDoanVao_sua(){
		SelectItem[] select = this.listItemMucDich();
		this.setListMucDich(select);
		return "finish_sua_doanvao";
	}
	
	public String insertDoanVao(){
		this.maDoan = null;
		this.setErrorTTChung("");
		this.setErrorVBDen("");
		this.setErrorVBNhan("");
		
		if (this.checkInputValue()){
			
			//lay ra ma muc dich
			try{
				//Lay id cua muc dich
				MucDich mucDich = new MucDich(this.linhVuc,this.mucDich);
				int idMucDich = mucDich.timLinhVuc();
				
				//Lay id quyet dinh
				VanBanDen vbDen = new VanBanDen(this.soVB,this.cqDeNghi, this.ngayXuatVB, this.noiDungVBDen, this.moTaQDDen);;
				int idVBDen = vbDen.insertVBDen();
				
				VanBanNhan vbNhan = new VanBanNhan(this.soUBNDTinh, this.ngayPhatHanh, this.baoCao, this.nguoiChuTri, this.moTaQDNhan);
				int idVBNhan = vbNhan.insertVBNhan();
				String madoan = managerID.getNewID("doanvao");
				String sql1 = "insert into doanvao(madoan,ngayden,ngaydi,idmucdich,diadiemlamviec,idvbden,idvbvao, mota,flagdelete) values('"+madoan+"','"+this.ngayDen+"','"+this.ngayVe+"',"+idMucDich+",'"+this.diaDiemDen+"',"+ idVBDen +","+idVBNhan+",'"+this.moTa+"',0)";
		
				int acc = truyXuatDatabase.insertData(sql1);
			if(acc == 1){
				this.maDoan = madoan;		
				
				this.linhVuc = "";
				this.mucDich = "";
				this.baoCao = false;
				this.tablesNguoiVao = null;
				this.moTa ="";
				this.cqDeNghi ="";
				this.diaDiemDen = "";
				this.errorVBDen ="";
				this.errorVBNhan ="";
				this.moTaQDDen="";
				this.moTaQDNhan ="";
				this.ngayPhatHanh =null;
				this.ngayDen =null;
				this.ngayVe = null;
				this.ngayXuatVB =null;
				this.nguoiChuTri = "";
				this.noiDungVBDen ="";
				this.soUBNDTinh ="";
				this.soVB ="";
			
					
				
				errorTTChung = "Đoàn đã được thêm với mã là "+this.maDoan;
				this.setErrorTTChung(errorTTChung);
			}
			else  {
				this.maDoan = null;
				errorTTChung ="Việc thêm đoàn ra đã xảy ra sự cố.Yêu cầu nhập lại thông tin";
				this.setErrorTTChung(errorTTChung);
			}
			}
			catch(Exception ex){
				System.out.println("Loi");
			}
			}
			else {
				String er = this.getErrorTTChung();
				this.setErrorTTChung(er);			
			} 
		
				return "finish_them_doanvao";
	}

	public SelectItem[] getListMucDich() {
		if (listMucDich==null){
			this.listMucDich = loadFirstItem();
		}
		return this.listMucDich;
	}
	int intPhanBietThemSuaDoanVao = 0;
	public String themThanhVien(){
		this.setErrorTTChung("");
		HttpSession ses = UtilsOnScopes.getSession();		
		ses.setAttribute("maDoanVao", this.maDoan);
		ses.setAttribute("phanbiet", intPhanBietThemSuaDoanVao);
		intPhanBietThemSuaDoanVao = 100;	
		return "finish_doanvao_themthanhvien";
	}
	public String huyInsert(){
		this.setErrorTTChung("");
		HttpSession ses = UtilsOnScopes.getSession();		
		ses.removeAttribute("maDoanVao");
		this.maDoan ="";
		this.linhVuc = "";
		this.mucDich = "";
		this.baoCao = false;
		this.tablesNguoiVao = null;
		this.moTa ="";
		this.cqDeNghi ="";
		this.diaDiemDen = "";
		this.errorVBDen ="";
		this.errorVBNhan ="";
		this.moTaQDDen="";
		this.moTaQDNhan ="";
		this.ngayPhatHanh =null;
		this.ngayDen =null;
		this.ngayVe = null;
		this.ngayXuatVB =null;
		this.nguoiChuTri = "";
		this.noiDungVBDen ="";
		this.soUBNDTinh ="";
		this.soVB ="";
		return "finish_them_doanvao";
	}
	//********************* Ket thuc them moi doan vao ****************
	
	//******************* Sua doan vao ************************
	

	public void setDsDoanVao(List<SuaDoanVao> dsDoanVao) {
		this.dsDoanVao = dsDoanVao;
	}

	int acc =1;
	public List<SuaDoanVao> getDsDoanVao() {	
		if(this.dsDoanVao == null) {
			acc =1;
			this.helpTimKiem();
		}
		return dsDoanVao;
	}
	
		public String helpTimKiem(){
		String result = "finish_sua_doanvao";		
		this.setErrorTTChung("");
		List<SuaDoanVao> list = new ArrayList<SuaDoanVao>();
		String SQL ="";
			if(acc == 0){
			SQL ="select * from doanvao as DV,mucdich as MD, vanbanden as VBD, vanbannhan as VBN where MD.id = DV.idmucdich and  VBD.id = DV.idvbden and VBN.id = DV.idvbvao and madoan = '"+this.maDoan+"' and flagdelete = 0";
			}
			else if(acc ==1){
				SQL = "select * from doanvao as DV, mucdich as MD, vanbanden as VBD, vanbannhan as VBN where MD.id = DV.idmucdich and VBD.id = DV.idvbden and VBN.id = DV.idvbvao and flagdelete = 0";
			}
			else if(acc ==2) {
				//Thong tin cua doan
				String strMaDoan ="";
				String strNgayDen = "";
				String strNgayVe = "";
				String strDiaDiemLamViec = "";
				String strMoTa ="";
				String strLinhVuc = "";
				String strMucDich ="";
				
				//Thong tin van ban den
				String strSoVB = "";
				String strCoQuanDeNghi = "";
				String strNgayXuatVB = "";
				String strNoiDung ="";
				String strMoTaQDDen = "";
				
				//Thong tin van ban nhan
				String strSoUBNDTinh = "";
				String strNgayPhatHanh ="";			
				String strNguoiChuTri = "";
				String strMoTaQDVao = ""; 
				
			
				//kiem tra thong tin cua doan 
				if(this.maDoan != null){
					if(this.maDoan.length() >0)
					strMaDoan ="%"+this.maDoan+"%";
					else strMaDoan = "%";
				}
				else strMaDoan = "%";
	
				if(this.diaDiemDen != null){
					if(this.diaDiemDen.length() > 0)
					strDiaDiemLamViec = "%"+this.diaDiemDen+"%";
					else strDiaDiemLamViec ="%";
				}
				else strDiaDiemLamViec ="%";
			
				if(this.moTa != null) {
					if(this.moTa.length() > 0)
					strMoTa = "%"+this.moTa+"%";
					else strMoTa = "%";
				}
				else strMoTa = "%";
				
				strLinhVuc = "%"+this.linhVuc+"%";
				
				strMucDich = "%"+this.mucDich+"%";
				
				if(this.ngayDen != null)  {
					if(!this.ngayDen.equals(""))
					strNgayDen = "DV.ngayden = '"+this.ngayDen+"' and ";
				}
				
				if(this.ngayVe != null) {
					if(!this.ngayVe.equals(""))
					strNgayVe  = "DV.ngaydi = '"+this.ngayVe+"' and ";
				}
				
				//kiem tra thong tin van ban den
				
				if(this.soVB != null) {
					if(this.soVB.length() > 0)
					strSoVB = "%"+this.soVB+"%";
					else strSoVB = "%";
				}
				else strSoVB = "%";
				

				if(this.cqDeNghi != null) {
					if(this.cqDeNghi.length() > 0)
					strCoQuanDeNghi = "%"+this.cqDeNghi+"%";
					else strCoQuanDeNghi = "%";
				}
				else strCoQuanDeNghi = "%";
				
				if(this.ngayXuatVB != null) {
					if(!this.ngayXuatVB.equals(""))
					strNgayXuatVB = "VBD.ngayxuatvb = '"+this.ngayXuatVB+"' and  ";
				}
				
				if(this.noiDungVBDen != null) {
					if(this.noiDungVBDen.length() > 0)
					strNoiDung = "%"+this.noiDungVBDen+"%";
					else strNoiDung = "%";
				}
				else strNoiDung = "%";
				
				if(this.moTaQDDen != null) {
					if(this.moTaQDDen.length() > 0)
					strMoTaQDDen = "%"+this.moTaQDDen+"%";
					else strMoTaQDDen = "%";
				}
				else strMoTaQDDen = "%";
				
				//Thong tin van ban nhan
				if(this.soUBNDTinh != null) {
					if(this.soUBNDTinh.length() > 0)
					strSoUBNDTinh = "%"+this.soUBNDTinh+"%";
					else strSoUBNDTinh = "%";
				}
				else strSoUBNDTinh = "%";
				
				if(this.ngayPhatHanh != null) {
					if(!this.ngayPhatHanh.equals(""))
					strNgayPhatHanh = "VBN.ngayphathanh = '"+this.ngayPhatHanh+"' and  ";
				}
				
				if(this.nguoiChuTri != null) {
					if(this.nguoiChuTri.length() > 0)
					strNguoiChuTri = "%"+this.nguoiChuTri+"%";
					else strNguoiChuTri = "%";
				}
				else strNguoiChuTri = "%";
				
				if(this.moTaQDNhan != null) {
					if(this.moTaQDNhan.length() > 0)
					strMoTaQDVao = "%"+this.moTaQDNhan+"%";
					else strMoTaQDVao = "%";
				}
				else strMoTaQDVao = "%";
				
				
				SQL ="select * from doanvao as DV,mucdich as MD, vanbanden as VBD, vanbannhan as VBN " +
				"where VBD.id = DV.idvbden and " +
				"VBN.id = DV.idvbvao and " +
				"MD.id = DV.idmucdich and " +
				"DV.madoan like '"+strMaDoan+"' and " +
				"DV.diadiemlamviec like '"+strDiaDiemLamViec+"' and " +
				"DV.mota like '"+strMoTa+"' and  "+
				strNgayDen+
				strNgayVe+
				"MD.linhvuc like '"+strLinhVuc+"' and "+
				"MD.mucdich like '"+strMucDich+"' and "+
				"VBD.sovb like '"+strSoVB+"' and "+
				"VBD.coquandenghi like '"+strCoQuanDeNghi+"' and "+
				strNgayXuatVB+
				"VBD.noidung like '"+strNoiDung+"' and "+
				"VBD.motaqd like '"+strMoTaQDDen+"' and "+
				"VBN.soubndtinh like '"+strSoUBNDTinh+"' and "+
				strNgayPhatHanh+
				"VBN.nguoichutri like '"+strNguoiChuTri+"' and "+
				"VBN.motaqd like '"+strMoTaQDVao+"' and "+
				"VBN.baocao = "+this.baoCao+
				" and flagdelete = 0"; 
	
			}

			else if(acc == 3){
				HttpServletRequest request = UtilsOnScopes.getRequest();
				String lnkMaDoan = request.getParameter("lnkMaDoan");
				System.out.println("LINK "+lnkMaDoan);
				SQL ="select * from doanvao as DV, vanbanden as VBD, vanbannhan as VBN, mucdich as MD where MD.id = DV.idmucdich and VBD.id = DV.idvbden and VBN.id = DV.idvbvao and madoan = '"+lnkMaDoan+"' and flagdelete = 0";
				System.out.println("VAO SQL "+SQL);
			}
		System.out.println("SQL "+SQL);
		ArrayList<HashMap<String, Object>> array = ConnectDB.getDataTypeList(SQL);
		if (array.size() > 0) {
			HashMap<String, Object> hash = new HashMap<String, Object>();
			int stt = 0;
			for (int i = 0; i < array.size(); i++) {
				
				//*********** Dua thong tin vao danh sach doan vao o phia duoi
				stt = i + 1;
				hash = (HashMap<String, Object>) array.get(i);
				SuaDoanVao suadoanvao = new SuaDoanVao();
				String madoanvao = hash.get("madoan").toString();
				String strNgayDen = hash.get("ngayden").toString();
				String strNgayVe = hash.get("ngaydi").toString();
				String strDiaDiemLamViec = hash.get("diadiemlamviec").toString();
				String strCoQuanDeNghi = hash.get("coquandenghi").toString();
			
				suadoanvao.setStt(String.valueOf(stt));
				suadoanvao.setNgayDen(strNgayDen);
				suadoanvao.setNgayDi(strNgayVe);
				suadoanvao.setDiaDiemLamViec(strDiaDiemLamViec);
				suadoanvao.setMaDoanVao(madoanvao);
				suadoanvao.setCoQuanDeNghi(strCoQuanDeNghi);
					
				//***************** Ket thuc dua thong tin ***************
				
				//******************* set thong tin len textbox
				if(array.size() == 1){
					this.setMaDoan(hash.get("madoan").toString());	
					this.setLinhVuc(hash.get("linhvuc").toString());
					this.setMucDich(hash.get("mucdich").toString());
					this.setNgayDen(DateUtils.sqlDateToutilDate(java.sql.Date.valueOf(strNgayDen)));
					this.setNgayVe(DateUtils.sqlDateToutilDate(java.sql.Date.valueOf(strNgayVe)));
					this.setDiaDiemDen(strDiaDiemLamViec);
					this.setMoTa(hash.get("mota").toString());
					
					this.setSoVB(hash.get("sovb").toString());
					this.setCqDeNghi(hash.get("coquandenghi").toString());
					this.setNgayXuatVB(DateUtils.sqlDateToutilDate(java.sql.Date.valueOf(hash.get("ngayxuatvb").toString())));
					this.setNoiDungVBDen(hash.get("noidung").toString());
					this.setMoTaQDDen(hash.get("motaqd").toString());
					
					this.setSoUBNDTinh(hash.get("soubndtinh").toString());
					this.setNgayPhatHanh(DateUtils.sqlDateToutilDate(java.sql.Date.valueOf(hash.get("ngayphathanh").toString())));
					this.setBaoCao(Boolean.parseBoolean(hash.get("baocao").toString()));
					this.setNguoiChuTri(hash.get("nguoichutri").toString());
					this.setMoTaQDNhan(hash.get("motaqd").toString());
				
					}
				else {
					this.maDoan ="";
					this.ngayDen = null;
					this.ngayVe = null;
					this.diaDiemDen = null;
					this.moTa = null;
					
					this.soVB = "";
					this.cqDeNghi = "";
					this.ngayPhatHanh = null;
					this.noiDungVBDen = null;
					this.moTaQDDen = null;
					
					this.soUBNDTinh ="";
					this.ngayPhatHanh =null;
					this.baoCao =false;
					this.nguoiChuTri ="";
					this.moTaQDNhan ="";
					
				}
				//************* Ket thuc set thong tin len textbox
				
				list.add(suadoanvao);
			}
		}
		else {
			if(this.maDoan != null)
			this.setErrorTTChung("Không tìm thấy đoàn có mã số đã nhập");
			this.maDoan ="";
			this.ngayDen = null;
			this.ngayVe = null;
			this.diaDiemDen = null;
			this.moTa = null;
			
			this.soVB = "";
			this.cqDeNghi = "";
			this.ngayPhatHanh = null;
			this.noiDungVBDen = null;
			this.moTaQDDen = null;
			
			this.soUBNDTinh ="";
			this.ngayPhatHanh =null;
			this.baoCao =false;
			this.nguoiChuTri ="";
			this.moTaQDNhan ="";
			
		}
		this.setDsDoanVao(list);
		return result;
	}
	public String displayAll(){
		acc =1;
		this.maDoan =null;
		this.helpTimKiem();
		return "finish_sua_doanvao";
	}
	public String timKiemTheoMa(){
		acc =0;
		this.helpTimKiem();
		return "finish_sua_doanvao";
	}
	public String timKiemTheoLink(){
		acc =3;
		System.out.println("Vao tim kiem theo link");
		this.helpTimKiem();
		return "finish_sua_doanvao";
	}
	
	//tim kiem mo rong
	public String timKiemMoRong(){
		acc =2;
		this.helpTimKiem();
		return "finish_sua_doanvao";
	}
	

	public String capNhatDoanVao(){
		System.out.println("Vao cap nhat doan vao");
		if(truyXuatDatabase.getDuyet(this.maDoan) == -1) {
			this.setErrorTTChung("Bạn phải gửi yêu cầu đến người quản trị trước khi sửa đoàn ra này!");
		}
		else if(truyXuatDatabase.getDuyet(this.maDoan) == 0) {
			this.setErrorTTChung("Yêu cầu của bạn đang ở trạng thái chờ duyệt");
		}
		else if(truyXuatDatabase.capNhatDoanVao(this.maDoan,DateUtils.utilDateToSqlDate(this.ngayDen), DateUtils.utilDateToSqlDate(this.ngayVe), this.mucDich, this.linhVuc, this.diaDiemDen, this.soVB, this.cqDeNghi, DateUtils.utilDateToSqlDate(this.ngayXuatVB), this.noiDungVBDen, this.moTaQDDen, this.soUBNDTinh,DateUtils.utilDateToSqlDate(this.ngayPhatHanh), this.baoCao, this.nguoiChuTri, this.moTaQDNhan, this.moTa)){
				this.setErrorTTChung("Đoàn vào có mã "+this.maDoan+" đã được cập nhật thành công");
			}
		else this.setErrorTTChung("Quá trình cập nhật xãy ra lỗi");
		return "finish_sua_doanvao";	
	}
	
	
	public String capNhatThanhVien(){
		HttpServletRequest request = UtilsOnScopes.getRequest();
		String maDoan_TV = request.getParameter("maDoan_TV");
		if(truyXuatDatabase.getDuyet(maDoan_TV) == -1) {
			this.setErrorTTChung("Bạn phải gửi yêu cầu đến người quản trị trước khi sửa đoàn ra này!");
			return "fail_suadoanvao_capnhatthanhvien";
		}
		else if(truyXuatDatabase.getDuyet(maDoan_TV) == 0) {
			this.setErrorTTChung("Yêu cầu của bạn đang ở trạng thái chờ duyệt");
			return "fail_suadoanvao_capnhatthanhvien";
		}
		else{
		this.errorTTChung = "";
		intPhanBietThemSuaDoanVao = 200;		
		HttpSession ses = UtilsOnScopes.getSession();
		ses.setAttribute("maDoanVao",maDoan_TV);
		ses.setAttribute("phanbiet",intPhanBietThemSuaDoanVao);
		return "success_suadoanvao_capnhatthanhvien";
		}
		
	}

}
